"""
FIVEDASH(TM) -- General Purpose Accounting Software
Copyright (C) 2008  Obol Software Pty Ltd

This program is free software: you can redistribute it and/or modify
it. For terms and conditions, see fivedash/license/license.txt

"""

admin_action_map = {
        'get_user_id':			''' select nextval('system.user_id_seq') ''',
        'get_grant_id':			''' select nextval('system.grant_id_seq') ''',
        'update_user_passwd':	''' update system.user set encrypted_password = '%s' where name = '%s' ''',
        'get_person_name':		'''select id, given_name||' '||name as personname from system.entity where type='person' ''',
        'get_role':				'''select id,name from system.role''',
        'get_group':        "SELECT id,name FROM system.group WHERE name<>'root'",
        'get_user_info':		"select name,encrypted_password from system.user where name = '%s'",
        'create_db_user':		''' create user %s with login password '%s' ''',
        'grant_db_user':		''' grant %s to %s ''',
        'get_user_role':		''' select r.id,r.name from system.grant g,system.user u,system.role r where g.user_id = u.id and r.id = g.role_id and u.name = '%s' ''',
        'get_sys_code':			''' select code_value,code_desc from system.sys_code where code_id = '%s' ''',
        'get_country_code':             '''SELECT * FROM system.iso_country_code WHERE name = UPPER('%s') OR UPPER('%s') = ANY(alias)''',
        #'get_errors':			"SELECT * FROM system.error  ORDER BY id %s",
        #'get_error_by_id':		"SELECT * FROM system.error WHERE id = %s",
        #'update_error':		"UPDATE system.error SET name = '%s',description = '%s' WHERE id=%s",
        #'delete_error':		"DELETE FROM system.error WHERE id=%s",
}

app_admin_action_map = {
        #-------------------------------------liang
        'get_users':					"SELECT * FROM system.user WHERE is_active='TRUE' ORDER BY id %s",
        'get_company_roles':			"SELECT * FROM system.grant ORDER BY user_id,company_id,role_id",
        'app_get_user_company_by_id':	"SELECT DISTINCT b.id,b.name FROM system.grant a, system.company b, system.user c WHERE a.user_id = c.id and a.company_id = b.id and c.id = %s",
        'app_get_users_of_company': "SELECT a.*,b.name AS user_name,c.name AS group_name FROM system.affiliation a ,system.user b,system.group c WHERE a.user_id=b.id AND a.group_id=c.id AND a.company_id=%s ORDER BY a.user_id %s",
        'app_get_user_affiliation':"SELECT a.*,b.name AS user_name,c.name AS company_name,d.name AS group_name FROM system.affiliation a LEFT JOIN system.user b ON a.user_id = b.id LEFT JOIN system.company c ON a.company_id = c.id LEFT JOIN system.group d ON a.group_id = d.id WHERE user_id=%d AND company_id=%d AND group_id=%d",
        'delete_grants':				"DELETE FROM system.grant WHERE user_id=%s AND company_id=%s",
        'insert_grants':				"INSERT INTO system.grant VALUES(%s,%s,%s)",
        'delete_group': "DELETE FROM system.group WHERE id=%s",
        'get_all_grant':    "SELECT * FROM system.grant",
        'delete_grant_of_group':"DELETE FROM system.grant WHERE group_id=%s",
        'get_group_details':    "SELECT * FROM system.group WHERE id=%s",
        'get_group_grant_details':  "SELECT group_id,role_id,ro.name AS role_name FROM system.grant gt LEFT JOIN system.role ro ON gt.role_id=ro.id WHERE gt.group_id=%s",
		'check_group_grant':  "SELECT DISTINCT id,count(role_id) AS grant_role FROM system.group gp LEFT JOIN system.grant gt ON gt.group_id=gp.id GROUP BY id",
		'check_user_grant':  "SELECT DISTINCT id,count(company_id) AS grant_role FROM system.user ur LEFT JOIN system.affiliation an ON ur.id=an.user_id GROUP BY id",
        'get_user_rights':  "SELECT a.user_id,u.name AS user_name,a.company_id,c.name AS company_name,a.group_id,g.name AS group_name FROM system.affiliation a LEFT JOIN system.user u ON a.user_id=u.id LEFT JOIN system.company c ON a.company_id=c.id LEFT JOIN system.group g ON a.group_id=g.id WHERE a.company_id<>0 %s and a.user_id not in (select id from system.user where is_active='FALSE') ORDER BY a.user_id,a.company_id,a.group_id %s",
        'revoke_user':  "DELETE FROM system.affiliation WHERE user_id=%d AND company_id=%d AND group_id=%d",
        'delete_user':  "UPDATE system.user SET is_active='FALSE' WHERE id=%s",
        'remove_right_on_user':  "DELETE FROM system.affiliation WHERE user_id=%s",
        'edit_user_group':"UPDATE system.affiliation SET group_id=%s WHERE user_id=%s AND company_id=%s AND group_id=%s",
        'get_user_group':"SELECT company_id,group_id FROM system.affiliation WHERE user_id=%s",
        'change_skin':"UPDATE system.user SET skin='%s' WHERE id=%s",
        #--------------------------liang
        'app_get_user_role':			''' select c.name as companyname,d.name as rolename,c.db_conn_str from system.grant a, system.user b, system.company c, system.role d where a.user_id = b.id and a.company_id = c.id and a.role_id = d.id and b.name = '%s' and a.company_id = %s ''',
        'app_get_user_role_cnd':		''' select b.name as username, c.name as companyname,d.name as rolename,c.db_conn_str,c.id from system.grant a, system.user b, system.company c, system.role d where a.user_id = b.id and a.company_id = c.id and a.role_id = d.id %s  order by b.name,c.name,d.name limit %d offset %d ''',
        'app_get_user_company':			''' select distinct b.id as company_id,b.name as company_name,b.db_conn_str  from system.grant a, system.company b, system.user c where a.user_id = c.id and a.company_id = b.id and c.name = '%s' ''',
        'app_get_user_info':			''' select * from system.user where name = '%s' ''' ,
        'app_verify_user_passwd':       ''' select name from system.user where name = '%s' and passwd = crypt('%s', passwd) ''',
        'app_get_company_info':			''' select a.*,b.name as top_company  from system.company a left join system.company b  on a.top_id = b.id  where a.id = %s order by id ''',
        'app_get_all_company':			''' select * from system.company ''',
        'app_get_company_entity_id':    "SELECT code_value FROM system.sys_code WHERE code_id='this_entity_id'",
        'app_get_company_address_id':    "SELECT code_value FROM system.sys_code WHERE code_id='this_address_id'",
        'app_get_abn_info': "SELECT detail FROM system.contact_detail WHERE entity_id=%s AND type='abn'",
        'app_get_all_others_company':	''' select * from system.company where id <> %s''',
        'app_is_company_exist':			''' select * from system.company where name = '%s' ''',
        'app_get_id':					''' select nextval('%s') ''',
        'app_update_user_passwd':		''' update system.user set passwd = crypt('%s',gen_salt('bf')) where name = '%s' ''',
        'app_get_company_view':			''' select a.id,a.name,a.db_conn_str,b.name as top_company  from system.company a left join system.company b  on a.top_id = b.id order by id ''',  
        #-----------------------------rachel
        'get_company_group':	'''select company_id, group_id from system.affiliation as af, system.user as us where us.name='%s' and af.user_id=us.id order by user_id %s''',
        'user_is_root':         '''select MIN(id)=%d as is_root from system.user''',
##        'group_is_root':		'''select lower(name)='root' as is_root from system.group where id = %d''',  #hard code 'root'
        'get_company_name':		'''select name,logo_url,db_name from system.company where id = %d''',
        'get_role_by_group':	'''select ro.id,name from system.role as ro , system.grant as gr where gr.group_id = %d and ro.id = gr.role_id order by ro.id''',
        'get_affiliation_info':	'''select * from system.user as us, system.affiliation as af where us.name='%s' and af.user_id=us.id and af.company_id=%d and af.group_id=%d''',
        'get_company_by_id':	'''select id,name,db_ip,db_name,db_user,db_passwd, top_id ,logo_url from system.company where id=%d''',
        'get_group_by_name':	'''select * from system.group where name='%s' ''',
        'get_all_company':		'''select * from system.company order by id''',
        'get_all_group':		"select * from system.group order by id %s",
        'get_all_role':			'''select * from system.role order by id''',
        'get_group_id':			'''select nextval('system.group_id_seq') as id''',
        'get_company_id':		'''select nextval('system.company_id_seq') as id ''',
        'get_system_user_id':	'''select nextval('system.user_id_seq') as id''',
        'insert_user':			'''insert into system.user (id, name, passwd) values (%d, '%s', crypt('%s', gen_salt('bf'))) ''',
        'insert_group':			'''insert into system.group (id, name) values (%d, '%s')''',
        'config_grant':			'''insert into system.grant (group_id, role_id) values (%d, %d)''',
        'assign_user':			'''insert into system.affiliation (user_id, company_id, group_id) values (%d, %d, %d)''',
        'active_user':      "UPDATE system.user SET is_active='true',passwd=crypt('%s',gen_salt('bf')) WHERE id=%s",
        'user_have_right':  "SELECT * FROM system.affiliation WHERE user_id=%d AND company_id=%d",
        'get_iso_country_code':         '''select * from system.sys_code where code_id = 'iso_country_code' ''',
        'get_tax_page_md5':             '''select * from system.sys_code where code_id = 'tax_page_md5' ''',
        'update_tax_page_md5':          '''update system.sys_code set code_value='%s' where code_id='tax_page_md5' ''',
        'get_current_tax_page_id':         '''select MAX(tax_page_id) as current_tax_page_id from system.transaction_tax''',
        'get_default_inv_terms': "SELECT * FROM system.sys_code WHERE code_id='default_inv_terms'",
        'update_obsolete_tax':          '''update system.transaction_tax set obsolete = 'NOW' where obsolete IS NULL AND tax_page_id != 0''',
		'obsolete_user_input_tax':		'''UPDATE system.transaction_tax SET obsolete = 'NOW' WHERE tax_page_id = 0 AND obsolete IS NULL AND shortname IN (SELECT shortname FROM (SELECT COUNT(shortname) AS count, shortname FROM system.transaction_tax WHERE obsolete IS NULL GROUP BY shortname) AS foo WHERE count != 1)''',
        'insert_transaction_tax':       '''insert into system.transaction_tax (shortname, name, tax_page_id, rate, included_in_unit_price_default, track_on_purchases) values ('%s', '%s', %d, %s, '%s', '%s')''',
        'get_all_entities_not_sales_agent':     '''SELECT id, type, CASE WHEN given_name IS NULL THEN name ELSE given_name||' '||name END AS name FROM system.entity WHERE type = '%s' AND id NOT IN (SELECT entity_id FROM sales_agent)''',
        'insert_sales_agent':       '''INSERT INTO sales_agent VALUES (DEFAULT, %d, %d)''',
		'update_user_portal_order':"UPDATE system.user SET role_id='%s' WHERE id=%d",
}

crm_action_map = {
        'get_place_of_business_info1':	"SELECT * FROM system.company_location_info WHERE phone LIKE '%s%%'",
        'get_place_of_business_info2':	"SELECT * FROM system.company_location_info WHERE fax LIKE '%s%%'",
        'get_place_of_business_info3':	"SELECT * FROM system.company_location_info WHERE company_id = %s",
        #'get_companies':				"SELECT id, name FROM system.company ORDER BY name",
        'get_companies':				"SELECT id, name FROM system.entity WHERE type<>'person' ORDER BY name",
        'get_c_entity_types':			"SELECT * from system.entity_type WHERE id<>'person'",
        #'get_pop_details':				"SELECT phone, fax FROM system.place_of_business WHERE id=%s",
        'get_pop_details':				"SELECT phone, fax FROM system.location WHERE id=%s",
        'insert_entity':				"INSERT INTO system.entity (type) VALUES ('%s')",
        'insert_person2':				"INSERT INTO system.person (entity_id, place_of_business_id, %s) VALUES (%s, %s, '%s')",
        'insert_company2':				"INSERT INTO system.company (entity_id, name) VALUES (%s, '%s')",
        #'get_company_id4':				"SELECT id FROM system.company WHERE oid=%s",
        #'get_pop_id1':					"SELECT id FROM system.place_of_business WHERE oid=%s",
        #'get_pop_id2':					"SELECT id FROM system.place_of_business WHERE company_id = %s AND name = 'unknown'",
        'get_pop_id2':					"SELECT id FROM system.location WHERE entity_id = %s",
        #'get_pop_details2':			"SELECT phone, fax, name FROM system.place_of_business WHERE id=%s",
        'get_pop_details2':				"SELECT phone, fax, name FROM system.location WHERE id=%s",
        'insert_person3':				"INSERT INTO system.person (entity_id, %s) VALUES (%s,'', '%s')",
        'insert_alt_location':			"INSERT INTO system.alt_location (person_id, type, %s) VALUES (%s, 'work', '%s')",
        #'get_person_id4':				"SELECT id FROM system.person WHERE oid=%s",
        #'get_company_info':			"SELECT id,name FROM customer_info WHERE company_id=%s",
        'get_customer_company_info':	"SELECT id,name FROM customer_info WHERE company_id=%s",
        #'get_company_info2':			"SELECT name FROM system.company WHERE entity_id=%s",
        'get_company_info2':			"SELECT name FROM system.entity WHERE id=%s",
        #'get_pop_info1':				"SELECT * FROM system.place_of_business WHERE company_id=%s AND name <> 'unknown' LIMIT 2 OFFSET %s",   
        'get_pop_info1':				"SELECT * FROM system.location WHERE entity_id=%s",
        'get_address_info':				"SELECT * FROM system.address WHERE id='%s'",
        'get_person_id_list':			"SELECT person_id FROM contact_info where company_id=%s",
        'get_person_name2':				"SELECT e.given_name||' '||e.name as person_name FROM system.entity e WHERE type='person' AND id=%s",
        'get_log_entry':				"SELECT entity_id, comment, filename, action FROM contact_log WHERE id=%s",
        'get_customer_id2':				"SELECT id FROM customer_info WHERE person_id=%s",
        #'insert_contact_log':			"INSERT INTO contact_log (id, person_id, comment, filename, action) VALUES (%i, %i, %s, %s, %s)",
        'insert_contact_log':			"INSERT INTO contact_log (id, entity_id, comment, filename, action) VALUES (%i, %i, '%s', '%s', '%s')",
        'update_contact_log1':			"UPDATE contact_log SET comment = '%s', action = '%s', closed = 'today' WHERE id = %s",
        'update_contact_log2':			"UPDATE contact_log SET comment = '%s', filename = '%s', action = '%s', closed = 'today' WHERE id = %s",
        'update_contact_log3':			"UPDATE contact_log SET comment = '%s', action = '%s' WHERE id = %s",
        'update_contact_log4':			"UPDATE contact_log SET comment = '%s', filename = '%s', action = '%s' WHERE id = %s",
        #'get_contact_info':			"SELECT * FROM contact_info WHERE person_id=%s",
        'get_contact_info':				"SELECT * FROM contact_info WHERE person_id=%s AND company_id %s",
        #'get_person_info':				"SELECT * FROM system.person WHERE id=%s",
        'get_person_info':				"SELECT e.id,e.given_name,e.name AS surname,c.position,c.email,c.mobile,c.sms FROM system.entity e,system.abn_info c WHERE e.id = %s AND e.id = c.entity_id",
        'get_person_contact_info':		"SELECT * FROM system.contact_detail WHERE entity_id = %s",
        #'get_work_details':			"SELECT * from system.alt_location WHERE type = 'work' AND person_id=%s",
        'get_work_details':				"SELECT * from system.location WHERE name = 'work' AND entity_id=%s AND deactivated IS NULL",
        'get_pop_info2':				"SELECT * FROM system.company_location_info WHERE id=%s",
        'get_alt_locations':			"SELECT * FROM system.person_location_info WHERE type NOT IN ('place of business', 'work') AND person_id=%s",
        'get_locations_by_name':		"SELECT * FROM system.person_location_info WHERE type='%s' AND person_id=%s",
        #'get_alt_locations':			"SELECT * FROM system.location WHERE name <> 'work' AND entity_id=%s",
        #'get_locations_by_name':		"SELECT * FROM system.location WHERE name='%s' AND entity_id=%s",
        'get_contact_log_id':			"SELECT NEXTVAL('contact_log_id_seq') as num",
        'create_contact':				"SELECT create_contact('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,'%s','%s','%s','%s','%s','%s','%s',%s,'%s','%s')",
        'get_entity_by_id':				"SELECT * FROM system.entity WHERE id = %s",
        'edit_other_location':			"SELECT edit_other_location(%s,'%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s,'%s')",
        'create_other_location':		"SELECT edit_other_location(%s,'%s','%s','%s','%s','%s','%s','%s','%s','%s',%s,%s,'%s')",
        'get_exist_alt_location_types':	"SELECT DISTINCT name FROM system.location WHERE entity_id = %s",
        'get_relation_ship':			"SELECT * FROM system.relationship WHERE entity_id=%s AND rel_entity_id=%s",
        'get_old_address_info':			"SELECT * FROM system.address WHERE id=%s AND ver=%s",
        'get_persons1':                  "select person_id, given_name, surname from contact_info where person_id <> %s", 
        'get_persons2':                  "select * from contact_info where person_id = %s", 
        'get_location_id':      "select location_id from system.relationship where entity_id = %s and rel_entity_id = %s", 
        'get_person_locations1':        "select id, entity_id, name, address_id, 'p_0'||'_'||%s||'_'||id as combination, ' ' as checked from system.location where entity_id = %s and deactivated is null" , 
        'get_person_locations2':        "select id, entity_id, name, address_id, 'c_'||entity_id||'_'||%s||'_'||id as combination, ' ' as checked from system.location where id = %s and deactivated is null union all select id, entity_id, name, address_id, 'p_0'||'_'||%s||'_'||id as combination, ' ' as checked from system.location where entity_id = %s and deactivated is null", 
        'get_person_locations3':        "select id, entity_id, name, address_id, 'c_'||entity_id||'_'||%s||'_'||id as combination, ' ' as checked from system.location where id = %s and deactivated is null %s union all select id, entity_id, name, address_id, 'p_0'||'_'||%s||'_'||id as combination, ' ' as checked from system.location where entity_id = %s and deactivated is null", 
}

finance_action_map = {
        'get_link_candidates':		"SELECT * FROM system.business_objects",
        'get_link_objects':			"SELECT * FROM system.%s",
        'get_accounts':				"SELECT id, name, bal('%s'||'_'||id) as s_id FROM ledger.%s_info",
        'get_account_type':			"SELECT * FROM system.account_type",
        'get_same_account':			"SELECT id,name FROM ledger.general_chart_of_accounts WHERE id='%s' OR name='%s'",
        'get_same_type_accounts':               "SELECT * FROM ledger.general_chart_of_accounts WHERE account_type_id = '%s'",
        'get_same_type_taxes':            "SELECT t.* FROM system.linked_account AS l, system.transaction_tax AS t WHERE l.transaction_type = '%s' AND t.shortname = l.transaction_tax_shortname AND t.obsolete IS NULL",
        'insert_account':			"INSERT INTO ledger.general_chart_of_accounts (id, name,account_type_id,shortname) values ('%s', '%s', '%s','%s')",
        'default_tax_is_null':          "SELECT code_value='' AS bool FROM system.sys_code WHERE code_id = 'default_tax'",
        'update_default_tax':           "UPDATE system.sys_code SET code_value = (SELECT shortname FROM system.transaction_tax ORDER BY id LIMIT 1) WHERE code_id = 'default_tax'",
        'get_default_tax':                         "SELECT id AS default_tax_id, shortname, rate FROM system.transaction_tax WHERE shortname = (SELECT code_value FROM system.sys_code WHERE code_id = 'default_tax') AND obsolete IS NULL",
        'get_tax':					"SELECT * FROM system.transaction_tax WHERE obsolete IS NULL ORDER BY id",
        'get_ptax':					"SELECT tt.*,case when dt.code_value IS NULL then 'false' else 'true' end as default_flag,case when dt.code_value IS NULL then 'none' else 'block' end as isdefault  FROM (select a.* from system.transaction_tax a,system.linked_account b where a.shortname=b.transaction_tax_shortname and a.obsolete is null and b.transaction_type='retail purchase') as tt left join (select * from system.sys_code where code_id = 'default_tax') dt on tt.shortname = dt.code_value",
        'get_invoicer':				"SELECT name, abn, line1, line2, suburb, region, code, country FROM system.abn_info c, system.entity e, system.address a, (SELECT CAST(ent.code_value AS INT) AS id, CAST(add.code_value AS INT) AS address_id FROM system.sys_code ent, system.sys_code add WHERE ent.code_id = 'this_entity_id' AND add.code_id = 'this_address_id') AS this WHERE c.entity_id = e.id AND e.id = this.id AND this.address_id = a.id",
        'get_buyer':				"SELECT name, abn, line1, line2, suburb, region, code, country FROM system.abn_info c, system.entity e, system.address a, (SELECT CAST(ent.code_value AS INT) AS id, CAST(add.code_value AS INT) AS address_id FROM system.sys_code ent, system.sys_code add WHERE ent.code_id = 'this_entity_id' AND add.code_id = 'this_address_id') AS this WHERE c.entity_id = e.id AND e.id = this.id AND this.address_id = a.id",
        'get_invoicer_id':			"SELECT NEXTVAL('system.invoice_id_seq') AS invoice_id",
        'get_purchase_order_id':	"SELECT NEXTVAL('system.purchase_order_id_seq') AS purchase_order_id",
        'get_curr_invoicer_id':		"SELECT CURRVAL('system.invoice_id_seq') AS invoice_id",
        'get_invoices1':			"SELECT * FROM system.invoice_info WHERE id = %d",
        'get_invoices2':			"SELECT * FROM system.invoice_info WHERE id >= %d",
        'get_invoices3':			"SELECT * FROM system.invoice_info WHERE id IN %s",
        'get_purchase_orders1':			"SELECT * FROM system.purchase_order_info WHERE id = %d",
        'get_purchase_orders2':			"SELECT * FROM system.purchase_order_info WHERE id >= %d",
        'get_invoice_items_info':		"SELECT * FROM system.invoice_item_info WHERE invoice_id = %d",	
        'get_purchase_order_items_info':		"SELECT * FROM system.purchase_order_item_info WHERE purchase_order_id = %d",	
        'get_items':				"SELECT ji.id, description, amount FROM system.ar_journal_item ji, ledger.ar_chart_of_accounts coa WHERE ji.account_id = coa.id AND ji.drcr = 'DR' AND ji.id NOT IN (SELECT journal_item_id FROM system.invoice_item) AND link_object_id(coa.name)=%s",
        'get_remit_msg':        "SELECT code_desc FROM system.sys_code WHERE code_id = 'remit_msg' ORDER BY code_value",
        'get_candidates':			"SELECT * FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname IS NULL OR subsidiary_shortname=''",
        'get_coa':					"SELECT * FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname IS NULL OR subsidiary_shortname='' ORDER BY id",
        'get_coa2':					"SELECT * FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname = '%s'",
        #'get_baccounts':			"SELECT * FROM ledger.general_chart_of_accounts WHERE account_type_id = 'EXPENSE' AND subsidiary_shortname IS NULL",
        'get_equip_account':			"SELECT * FROM system.sys_code WHERE code_id = 'special_ac_equip'",
        'get_scoa':					"SELECT * FROM ledger.%s_info",
        'get_accounts2':			"SELECT id, name,subsidiary_shortname, bal('general_'||id) as gid,account_type_id as type FROM ledger.general_chart_of_accounts ORDER BY account_type_id,id",
        'get_accounts_type':    "SELECT id AS name FROM system.account_type WHERE id IN (SELECT DISTINCT account_type_id FROM ledger.general_chart_of_accounts) ORDER BY ord;",
        'update_journal':			"UPDATE system.journal SET posted = 'NOW' WHERE ID = %s",
        'update_general_accounts':	"UPDATE ledger.general_chart_of_accounts SET subsidiary_id = nextval('subsidiary_id_seq'), opening_balance = (SELECT bal('general_%s')) WHERE id='%s'",
        'create_subledger':			"SELECT create_subledger('%s','%s')",
        'get_journals':				"SELECT * FROM system.journal j WHERE ledger_shortname %s AND (SELECT SUM(amount) FROM system.general_journal_item ji WHERE ji.journal_id = j.id AND drcr = 'CR') - (SELECT SUM(amount) FROM system.general_journal_item ji WHERE ji.journal_id = j.id AND drcr = 'DR') = 0 AND posted IS NULL",
        'insert_general_item':		"INSERT INTO system.general_journal_item (id, journal_id, description, account_id, amount, drcr) values (DEFAULT, %s, '%s', '%s', %f, '%s')",
        #TODO:sl is useless
        'insert_sub_item':			"INSERT INTO system.sl%s_journal_item (id, journal_id, description, account_id, amount, drcr) VALUES (DEFAULT, %s, '%s', %s, %s, '%s')",
        'insert_journal':			"INSERT INTO system.journal (id, date) VALUES (DEFAULT, '%s')",
        'insert_journal2':			"INSERT INTO system.journal (id, date, ledger_shortname, posted) VALUES (%s, '%s', '%s', '%s')",
        'insert_journal3':			"INSERT INTO system.journal (id, date, ledger_shortname) VALUES (%s, '%s', '%s')",
        'get_curr_journal_id':		"SELECT CURRVAL('system.journal_id_seq') AS journal_id",
        'get_connections_to_bill':	"SELECT cx.id, cx.site_id, cx.customer_id, b.fee, ac.id AS account_id FROM connection cx, billing_plan b, ledger.%s_chart_of_accounts ac , customer cust WHERE cx.billing_plan_id = b.id AND cx.customer_id = cust.id AND link_object_id(ac.name) = cust.id AND link_object(ac.name) = 'customer' AND b.fee_type = 'monthly'",
        'get_same_billing_log':		"SELECT id from billing_log where fee_type='%s' and billed_month_year=%s and billed_month=%s",
        'get_next_journal_id':		"SELECT NEXTVAL('system.journal_id_seq') AS journal_id",
        'get_journal2':				"SELECT * FROM system.journal WHERE id =%s",
        'get_total':				"SELECT SUM(amount) as total FROM system.ar_journal_item WHERE journal_id = %s",
        'insert_billing_log':		"INSERT INTO billing_log (id, log_date, fee_type, billed_month_year, billed_month) VALUES (%s, '%s', '%s', %s, %s)",
        'insert_sub_account':		"INSERT INTO ledger.%s_chart_of_accounts (name) values ('%s%s')",
        'get_pk':					"SELECT * FROM system.pk_lookup WHERE table_schema = '%s' and table_name = '%s'",
        'get_accounts3':			"SELECT id, name, bal('%s'||'_'||id) as s_id FROM ledger.%s_chart_of_accounts ORDER BY id",
        'get_accounts_id':			"SELECT id FROM ledger.ar_chart_of_accounts",
        'get_items2':				"SELECT ji.id, description, amount FROM system.ar_journal_item ji WHERE ji.drcr = 'DR' AND ji.id NOT IN (SELECT journal_item_id FROM system.invoice_item) AND ji.account_id=%s",
        'insert_invoice':			"INSERT INTO system.invoice (id, date,journal_id) VALUES (%d, '%s', %d)",
        'get_invoice_id':			"SELECT currval('system.invoice_id_seq') as id",
        'get_invoice_item_id':		"SELECT nextval('system.invoice_item_id_seq') as invoice_item_id",
        'insert_invoice_item_tax':	"INSERT INTO system.invoice_item_tax (invoice_item_id, transaction_tax_id) VALUES (%d, %d)",
        'insert_invoice_item':		"INSERT INTO system.invoice_item (id, invoice_id, qty, description, unit_price, tax_included) VALUES (%d, %d, %d, '%s', %f, '%s')",
        'get_purchase_item_id':		"SELECT NEXTVAL('system.purchase_order_item_id_seq') as purchase_item_id",
        'insert_purchase_item':		"INSERT INTO system.purchase_order_item (id,purchase_order_id,qty,unit_price,tax_included) VALUES (%i,%i,%i,%f,%s)",
        'insert_purchase_item_tax':	"INSERT INTO system.purchase_item_tax(purchase_order_item_id,transaction_tax_id) VALUES(%i,%i)",
        'get_next_ap_journal_id':	"SELECT NEXTVAL('system.ap_journal_item_id_seq') AS ap_journal_id",
        'get_customers':			"SELECT * FROM customer_info",
        #'get_suppliers':			"SELECT * FROM supplier_info where id in(select split_part(ac.name,':',2)::integer from ledger.ap_info ai,ledger.ap_chart_of_accounts ac where ac.id = split_part(ai.id,'.',2)::integer) %s",
		'get_suppliers':			"SELECT * FROM supplier_info",
        'get_delivery_address':  "select b.id AS location_id,a.id,a.line1,a.line2,a.suburb,a.region,a.code,a.country,b.name,b.phone,b.fax from system.address a,system.location b where a.id = b.address_id and b.entity_id = %s",
        'get_items3':				"SELECT ji.id, 0 as state FROM system.ar_journal_item ji WHERE ji.drcr = 'DR' AND ji.id NOT IN (SELECT journal_item_id FROM system.invoice_item) AND ji.id IN (%s)",
        'get_table_name':			"SELECT find_name('%s')",
        'get_control_account_id':	"SELECT id FROM ledger.general_chart_of_accounts WHERE subsidiary_shortname ='%s'",
        'get_journals2':			"SELECT * FROM system.journal j WHERE ledger_shortname IS NULL AND posted IS NULL",				
        'get_account2':				"SELECT * FROM ledger.%s_chart_of_accounts WHERE id = %s",
        'get_sub_account_name':		"SELECT * FROM ledger.%s_chart_of_accounts WHERE name = '%s'",
        'get_account':				"SELECT * FROM ledger.general_chart_of_accounts WHERE id='%s'",
        'get_journal_items':		"SELECT * FROM system.general_journal_item WHERE journal_id = %i ORDER BY account_id",				 
        'get_sub_journal_items':	"SELECT * FROM system.%s_journal_item WHERE journal_id = %s",
        'get_sl_account_id':		"SELECT * FROM ledger.%s_info WHERE name = '%s'",
        'get_gen_account_id':		"SELECT * FROM ledger.general_chart_of_accounts WHERE lower(subsidiary_shortname) = lower('%s')",
        'insert_sl_journal_item':	"INSERT INTO system.%s_journal_item (id,journal_id, description, account_id, amount, drcr) VALUES (%i, %i, '%s', %i, %f, '%s')",
        'get_curr_sl_journal_id':	"SELECT CURRVAL('system.%s_journal_item_id_seq') AS sl_journal_id",
        'create_account':			"SELECT create_account('%s','%s','%s','%s','%s')",
        'post_journal':				"SELECT post_journal(%s)",
        'create_journal':			"SELECT create_journal('%s','{%s}','{%s}','{%s}','{%s}','{%s}','{%s}','{%s}','{%s}','%s')",
        'edit_journal':				"SELECT edit_journal(%s,'{%s}',%s,'%s','{%s}','{%s}','{%s}','{%s}','{%s}','%s','{%s}','{%s}','{%s}','{%s}','%s')", 
        'create_invoice':			"SELECT create_invoice('%s','%s','%s','%s','{%s}',E'{%s}','{%s}','{%s}','{%s}')",
        'get_invoices':				"SELECT i.id,i.date FROM system.journal j,system.invoice i WHERE i.journal_id = j.id AND j.ledger_shortname = 'ar'",
        'get_delivery_invoices':				"SELECT * FROM system.invoice_delivery_report ORDER BY invoice_id,date",
        'get_purchase_orders':				"SELECT id,date FROM system.purchase_order",
        'delete_journal':			"DELETE FROM system.journal WHERE id=%s",
        'create_purchase_order':			"SELECT create_purchase_order('%s','%s','%s','%s','%s','{%s}',E'{%s}','{%s}','{%s}','{%s}')",
        'get_next_expense_report_id':   "SELECT MAX(id)+1 AS id FROM system.expense_report",
        'create_expense_report':        "SELECT create_expense_report('%s', '%s', '%s', '{%s}', '{%s}', '{%s}', '%s', '%s')",
        'generate_receipt':             "SELECT generate_receipt('%s', '%s', '%s', '%s', '%s', '{%s}')",
		'get_linked_account_details':	"SELECT la.* FROM system.linked_account la,system.transaction_tax tt WHERE la.transaction_tax_shortname = tt.shortname AND tt.obsolete IS NULL ORDER BY transaction_tax_shortname",
		'get_linked_account_of_tax':	"SELECT * FROM system.linked_account WHERE transaction_tax_shortname='%s' AND transaction_type='%s'",
		'insert_linked_account':	"INSERT INTO system.linked_account(transaction_tax_shortname,transaction_type,account_id) VALUES('%s','%s','%s')",
		'update_linked_account':	"UPDATE system.linked_account SET account_id = '%s' WHERE transaction_tax_shortname = '%s' AND transaction_type = '%s'",
		'delete_linked_account':	"DELETE FROM system.linked_account WHERE transaction_tax_shortname = '%s' AND transaction_type = '%s'",
		'exist_special_params_len':	"SELECT count(*) AS special_params_len FROM (SELECT code_id FROM system.sys_code WHERE code_id IN %s GROUP BY code_id) AS sys_code",
		'check_special_params':	"SELECT * FROM system.sys_code WHERE code_id='%s' AND code_desc='%s'",
        'check_special_params2':	"SELECT * FROM system.sys_code WHERE code_id='%s' AND code_desc IS NULL",
		'get_special_params_details':	"SELECT * FROM system.sys_code WHERE code_id IN %s",
		'check_sys_code':	"SELECT * FROM system.sys_code WHERE code_id='%s' AND code_desc='%s'",
		'update_sys_code':	"UPDATE system.sys_code SET code_value='%s' WHERE code_id='%s' AND code_desc='%s'",
		'insert_sys_code':	"INSERT INTO system.sys_code VALUES('%s','%s','%s')",
		'delete_sys_code':	"DELETE FROM system.sys_code WHERE code_id='%s' AND code_desc='%s'",
		'get_accounts_by_type_without_sub':"SELECT * FROM ledger.general_chart_of_accounts WHERE account_type_id = '%s' AND subsidiary_shortname IS NULL",
}

operations_action_map = {
        'get_entity_types':						'SELECT id FROM system.entity_type',
        #'get_company_entities':				"SELECT c.id||':'||TRIM(COALESCE(abn, ' ')) as id, name FROM system.company c, system.entity e WHERE c.entity_id = e.id AND type = '%s' ORDER BY name",
        'get_company_entities':					"SELECT id||':'||TRIM(COALESCE(abn, ' ')) as id, name FROM system.entity e LEFT JOIN system.abn_info c ON c.entity_id = e.id WHERE e.type = '%s' ORDER BY name",
        #'get_person_entities':					"SELECT p.id||':'||TRIM(COALESCE(abn, ' ')) as id, given_name||' '||surname as name FROM system.person p, system.entity e WHERE p.entity_id = e.id ORDER BY surname, given_name",
        'get_person_entities':					"SELECT id||':'||TRIM(COALESCE(abn, ' ')) as id, given_name||' '||name as name FROM system.entity e LEFT JOIN system.abn_info c ON c.entity_id = e.id WHERE type='%s' ORDER BY name, given_name",
        'get_salesagents':						"SELECT id, COALESCE(name, given_name||' '||surname) as name FROM sales_agent_info",
        'get_sales_agents':     "select sa.id, sa.name, sa.given_name, sa.surname, sa.commission, case when cu.id IS NULL then -1 else cu.id end as customer_id from sales_agent_info sa left join customer cu on cu.sales_agent_id = sa.id",
        'get_alt_location_types':				"SELECT code_value FROM system.sys_code WHERE code_id = 'alt_location_type'",
        #'get_person_entity_id':				"SELECT entity_id FROM system.person WHERE id=%s",
        #'get_person_entity_id':				"SELECT id FROM system.entity WHERE id=%s",
        #'get_company_entity_id':				"SELECT entity_id FROM system.company WHERE entity_id=%s",
        #'get_company_entity_id':				"SELECT id FROM system.entity WHERE id=%s",
        'get_if_exist_abn':						"SELECT * FROM system.contact_detail WHERE entity_id=%s AND type='abn'",				
        #'update_abn':							"UPDATE system.entity SET abn='%s' WHERE id=%s",
        'update_abn':							"UPDATE system.contact_detail SET detail='%s' WHERE entity_id=%s AND type='abn'",
        #'insert_abn1':							"INSERT INTO system.entity (type, abn) VALUES ('%s','%s')",
        'insert_abn':							"INSERT INTO system.contact_detail(entity_id,type,detail) VALUES ('%s','abn','%s')",
        #'insert_person':						"INSERT INTO system.person (surname, given_name, entity_id) VALUES ('%s', '%s', %s)",
        'insert_person':						"INSERT INTO system.entity (name, given_name,type) VALUES ('%s', '%s', '%s')",
        #'insert_company':						"INSERT INTO system.company (name, entity_id) VALUES ('%s', %s)",
        'insert_company':						"INSERT INTO system.entity (name, type) VALUES ('%s','%s')",
        #'get_oid':								"SELECT id FROM system.entity WHERE oid=%s",
        'get_entity_id':						"SELECT CURRVAL('system.entity_id_seq') AS id",
        'get_entity_id2':						"SELECT entity_id FROM customer cu WHERE cu.id=%s",
        'get_person_id':						"SELECT id FROM system.person WHERE entity_id=%s",
        #'get_company_id':						"SELECT id FROM system.company WHERE entity_id=%s",
        #'get_pob_id':							"SELECT place_of_business_id FROM system.person WHERE id=%s",
        'get_pob_id':							"SELECT location_id FROM system.relationship WHERE type='employee' AND entity_id=%s AND rel_entity_id=%s",
        #'get_address_id1':						"SELECT address_id FROM system.place_of_business WHERE id=%s",					   
        #'get_address_id3':						"SELECT address_id FROM system.alt_location WHERE id=%s",
        'insert_address1':						"INSERT INTO system.address (line1, line2, suburb, region, code, country) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
        'get_address_id':						"SELECT address_id FROM system.location WHERE id='%s'",
        'insert_address2':						"INSERT INTO system.address (line1, suburb, region, code, country) VALUES ('%s', '%s', '%s', '%s', '%s')",
        #'get_address_id4':						"SELECT id FROM system.address WHERE oid=%s",
        'get_address_id4':						"SELECT CURRVAL('system.address_id_seq') AS id",
        #'insert_address3':						"INSERT INTO system.place_of_business (company_id, address_id, %s) VALUES (%s, %s, '%s')",
        'insert_address3':						"INSERT INTO system.location(entity_id, address_id, %s) VALUES (%s, %s, '%s')",
        #'insert_address4':						"INSERT INTO system.alt_location (person_id, address_id, %s) VALUES (%s, %s, '%s')",
        #'insert_address4':						"INSERT INTO system.location(entity_id, address_id, %s) VALUES (%s, %s, '%s')",
        #'insert_address5':						"INSERT INTO system.alt_location (person_id, address_id,type) VALUES (%s, %s, 'postal_address')",
        'insert_address5':						"INSERT INTO system.location(entity_id, address_id,name) VALUES (%s, %s, 'postal_address')",
        'insert_customer1':						"INSERT INTO customer (entity_id, sales_agent_id, billing_method, address_id) VALUES (%s, %s, '%s', %s)",
        'insert_customer2':						"INSERT INTO customer (entity_id, billing_method, address_id) VALUES (%s, '%s', %s)",
        'insert_customer3':						"INSERT INTO customer (entity_id, sales_agent_id, billing_method) VALUES (%s, %s, '%s')",
		'insert_customer4':						"INSERT INTO customer (entity_id, billing_method) VALUES (%s, '%s')",
        'insert_supplier':						"INSERT INTO supplier (entity_id, address_id) VALUES (%s,%s)",
        #'get_customer_id':						"SELECT id FROM customer WHERE oid=%s",
        'get_customer_id':						"SELECT CURRVAL('customer_id_seq') AS id",
        #'get_supplier_id':						"SELECT id FROM supplier WHERE oid=%s",
        'get_supplier_id':						"SELECT CURRVAL('supplier_id_seq') AS id",
        'get_next_ar_seq':						"SELECT NEXTVAL('ledger.ar_chart_of_accounts_id_seq') AS id",
        'get_next_ap_seq':						"SELECT NEXTVAL('ledger.ap_chart_of_accounts_id_seq') AS id",
        #'insert_ledger_ar_chart_of_accounts':	"INSERT INTO ledger.ar_chart_of_accounts (name) values ('#customer:%s')",
        'insert_ledger_ar_chart_of_accounts':	"SELECT create_account('ar','%s','#customer:%s','','')",				
        'insert_ledger_ap_chart_of_accounts':	"SELECT create_account('ap','%s','#supplier:%s','','')",
        'get_customer_billing_info':			"SELECT id,customer_name,billing_method,line1,line2,suburb,region,code,country,COALESCE(name, given_name||' '||surname)  AS sales_agent,entity_id FROM customer_billing_info where id=%d",
        #'get_rscount':							"SELECT count(customer_id) FROM system.invoice_info where customer_id=%d group by customer_id",
        'get_rscount':							"SELECT count(%s) FROM %s %s",
        'get_supplier_billing_info':        "SELECT id,supplier_name,line1,line2,suburb,region,code,country FROM supplier_billing_info where id=%d",
        'get_invoices_info':					"SELECT * FROM system.invoice_info where customer_id=%d ORDER BY id %s",
        'get_all_invoices':             "select * from system.invoice_info where id NOT IN (select i.id from system.invoice i, system.receipt r where i.id = ANY(r.invoice_id))",
        'get_all_purchase_orders':             "select * from system.purchase_order_info where id NOT IN (select p.id from system.purchase_order p, system.bill_payment b where p.id = ANY(b.purchase_order_id))",
        'get_purchase_orders_info':     "SELECT * FROM system.purchase_order_info where supplier_id=%d ORDER BY id %s",
        'get_customer_info':					"SELECT * FROM customer_info",
        'get_entity_type':						"SELECT type FROM customer_info WHERE id=%s",
        #'get_person_id2':						"SELECT p.id FROM system.person p, system.entity e, customer cu WHERE cu.entity_id = e.id AND p.entity_id = e.id AND cu.id=%s",
        'get_person_id2':						"SELECT e.id FROM system.entity e,customer cu WHERE e.type = 'person' AND cu.entity_id = e.id AND cu.id=%s",
        #'get_company_id2':						"SELECT c.id FROM system.company c, system.entity e, customer cu WHERE cu.entity_id = e.id AND c.entity_id = e.id AND cu.id=%s",
        'get_company_id2':						"SELECT e.id FROM system.entity e, customer cu WHERE e.type = 'company' AND cu.entity_id = e.id AND cu.id=%s",
        'get_site_id':							"SELECT site_id FROM connection",
        'get_billing_plan':						"SELECT id, name FROM billing_plan",
        'get_address_id2':						"SELECT address_id FROM system.place_of_business pob, system.person p WHERE p.place_of_business_id = pob.id AND p.id=%s",
        'get_person_id3':						"SELECT person_id FROM customer_info WHERE id=%s",
        'get_company_id3':						"SELECT company_id FROM customer_info WHERE id=%s",
        'insert_connection':					"INSERT INTO connection (site_id, customer_id, address_id, billing_plan_id) VALUES (%s, %s, %s, %s)",
        'get_company_ids_from_customer':		"SELECT company_id FROM customer_info",
        'get_person_ids_from_customer':			"SELECT person_id FROM customer_info",
        'get_company_ids_from_supplier':		"SELECT company_id FROM supplier_info",
        'get_person_ids_from_supplier':			"SELECT person_id FROM supplier_info",
        'get_all_purchase_tax_aggregation':      "SELECT * FROM system.purchase_order_tax_aggregation",
        'insert_contact_detail':	"INSERT INTO system.contact_detail VALUES(default,%s,'%s','%s')",
		'get_billing_methods':"SELECT code_value AS name FROM system.sys_code WHERE code_id='billing_method'",
		'get_customer_billing_details':"SELECT detail FROM system.contact_detail WHERE entity_id=%s AND type='%s'",
		#'get_customer_by_invoice_id':"SELECT c.* FROM customer c,system.invoice_info ii WHERE c.id=ii.customer_id AND ii.id=%s",
		'insert_invoice_delivery_report1':"INSERT INTO system.invoice_delivery_report VALUES(default,%s,default,'%s',%s)",
		'insert_invoice_delivery_report2':"INSERT INTO system.invoice_delivery_report (invoice_id,method,detail)VALUES(%s,'%s','%s')",
		'get_customer_by_id':"SELECT * FROM customer WHERE id=%s",
		'pay_bill':			"SELECT pay_bill('%s','%s','%s', '{%s}','{%s}','%s','%s','%s','{%s}')",
		'get_invoice_tax_aggregation':			"SELECT * FROM system.invoice_tax_aggregation WHERE invoice_id = %d",
		'get_po_tax_aggregation':			"SELECT * FROM system.purchase_order_tax_aggregation WHERE purchase_order_id = %d",
		'get_delivery_address_info':"SELECT a.*,l.name FROM system.location l,system.address a WHERE l.address_id=a.id AND l.id=%s",
}

report_action_map = {
##    'get_report_info':				"SELECT * FROM system.report WHERE id = %i",
##    'get_system_report_detail':		"SELECT rd.id AS detail_id, rd.section_id AS section_id, se.name AS section_name, rd.item_id AS item_id, it.name AS item_name, rd.params AS params FROM system.report_detail AS rd, system.report_section AS se, system.report_item AS it WHERE rd.report_id = %i AND it.flag = 0 AND se.id = rd.section_id AND it.id = rd.item_id ORDER BY detail_id",
##    'get_user_report_detail':		"SELECT rd.id AS detail_id, rd.section_id AS section_id, se.name AS section_name, rd.item_id AS item_id, it.name AS item_name, rd.params AS params FROM system.report_detail AS rd, system.report_section AS se, system.report_item AS it WHERE rd.report_id = %i AND it.flag = 1 AND se.id = rd.section_id AND it.id = rd.item_id ORDER BY detail_id",
##    'get_section':					"SELECT se.id AS section_id, se.name AS section_name,se.f_id FROM system.report_detail AS rd, system.report_section AS se WHERE rd.report_id=%i AND se.id=rd.section_id GROUP BY se.id, se.name,se.f_id",
##    #---------------liang 
##    'insert_report_item':			"INSERT INTO system.report_item (id,name,flag) VALUES (Default,'%s',1)",
##    'get_item_id':					"SELECT currval('system.report_item_id_seq') AS id",
##    'insert_report_detail':			"INSERT INTO system.report_detail(id,report_id,section_id,item_id,params) VALUES(Default,%s,%s,%s,'%s')",
##    'get_simple_report_details':	"SELECT s.id as section_id,s.name AS section_name,i.name AS item_name,i.flag,d.params FROM system.report_detail AS d,system.report_section AS s,system.report_item AS i WHERE d.report_id = %s AND d.section_id = s.id AND d.item_id = i.id ORDER BY d.section_id,d.item_id",
##    'get_sum':						"SELECT bal('general_'||id) AS sumamount FROM ledger.general_chart_of_accounts %s ORDER BY id",
##    'get_sum2':						"SELECT profit_bal('general_'||id) AS sumamount FROM ledger.general_chart_of_accounts %s ORDER BY id",
##    'update_report_detail':			"UPDATE system.report_detail SET params='%s' WHERE report_id=%s AND section_id=%s AND item_id=%s",
##    'get_report_item_by_name':		"SELECT * FROM system.report_item WHERE name='%s'",
##    'escape_repeat_details':		"SELECT id FROM system.report_detail WHERE report_id=%s AND section_id=%s AND item_id=%s",
##    #-------------liang
        'get_report_detail':            "SELECT * FROM system.report_detail WHERE report_id=%s order by order_num",
        'get_report_info':                "SELECT * FROM system.report WHERE id=%s",
        'get_report_sections':           "SELECT * FROM system.report_section WHERE report_id=%s and insert_position_id > 0",
        'update_params':                     "UPDATE system.report_detail SET params='%s' WHERE name||id='%s'" ,
        'get_max_id':                           "SELECT max(id) as max_id FROM system.report_detail WHERE report_id=%s",
        'get_insert_position1':           "SELECT order_num FROM system.report_detail WHERE id = (SELECT insert_position_id FROM system.report_section WHERE report_id=%s AND id=%s)",
        'get_insert_position2':             "SELECT max(order_num) as order_num FROM system.report_detail WHERE report_id=%s",
        'update_order_num':                 "UPDATE system.report_detail SET order_num=order_num+1 WHERE order_num>%s and report_id=%s",
        'insert_new_item':                  "insert into system.report_detail    values(default,%s,%s,'%s','%s',1,%s,%s)",
        'del_report_preresult':         "delete from system.report_result where report_id=%s",
        'get_item_info1':                      "select a.*,b.formula from system.report_detail a,system.report_formula b where a.report_formula_id = b.id and params <>'' and a.report_id=%s",
        'get_item_info2':                      "select a.*,b.formula from system.report_detail a,system.report_formula b where a.report_formula_id = b.id and item_flag=2 and a.report_id=%s",
        'get_item_info0':                      "select a.* from system.report_detail a where item_flag=0 and a.report_id=%s and section_id in(%s)" ,
        'insert_result':                       "insert into system.report_result values(%s,%s,%s)",
        'display_report':                      "select name,value,item_flag from system.report_detail rd,system.report_result rr where rd.id = rr.item_id and rd.report_id = %s order by order_num"
}

tax_action_map = {
        'get_tax_by_id':				''' select  a.id,b.formula as cal_formula,c.formula as cond_formula from system.tax_class a, system.tax_formula b,system.tax_formula c where a.calc_formula_id = b.id and a.cond_formula_id = c.id  and a.id = %s and ( a.effect_date = '19700101'  or to_date('%s','YYYYMMDD') > a.effect_date ) and ( a.expire_date = '21000101' or to_date('%s','YYYYMMDD') < a.expire_date ) ''',
}
